package com.wlyuan.open.scanner.dao.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.wlyuan.open.scanner.application.annotation.DataScope;
import com.wlyuan.open.scanner.dao.entity.SupplierDO;
import com.wlyuan.open.scanner.domain.carrier.Relation;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * Mapper 接口
 * </p>
 *
 * @author yuanjie
 * @since 2022-09-23
 */
public interface SupplierMapper extends BaseMapper<SupplierDO> {

    String QUERY_SQL = "SELECT a.Supplier_ID, a.Supplier_GUID, a.Supplier_OwnerCompanyID, a.Supplier_CompanyID, a.Supplier_Status, a.Supplier_StatusTime, a.Supplier_Processor, a.Supplier_Welcome, a.Supplier_Creator, a.Supplier_InsertTime, a.Supplier_Updater, a.Supplier_UpdateTime, a.Supplier_Invalid, a.Supplier_Comments, a.opt_status, a.Supplier_ClientCode, a.Supplier_AutoBalance, a.Supplier_ShortName, a.Supplier_Heat, a.Supplier_Type " +
            ",b.Company_Phone as contactMobile,b.Company_Mail as contactEmail,SUBSTRING(b.Company_Contact,0,50) as contactName,b.Company_Name as name,b.Company_Personal as personal, 1 as type,b.Company_Status " +
            "FROM TMS_MSupplier a " +
            "INNER JOIN TMS_Company AS b ON a.Supplier_CompanyID = b.Company_ID " +
            "WHERE a.Supplier_OwnerCompanyID > 0 and a.Supplier_CompanyID > 0";

    String RELATION_SQL = "select a.Supplier_ID as carrierId,a.Supplier_OwnerCompanyID as upstream,b.Customer_ID as customerId,b.Customer_OwnerCompany as downstream,a.Supplier_Welcome as welcome,a.Supplier_Status as supplierStatus,a.Supplier_Invalid as supplierInvalid," +
            "a.Supplier_InsertTime as createTime,a.Supplier_Creator as createBy,CASE when b.Customer_OwnerCompany=b.Customer_CompanyID THEN 2 ELSE b.Customer_UnderLine end as customerType " +
            "from TMS_MSupplier a " +
            "inner join TMS_MCustomer b on a.Supplier_OwnerCompanyID = b.Customer_CompanyID and a.Supplier_CompanyID = b.Customer_OwnerCompany " +
            " where a.Supplier_Status = 1 and a.Supplier_OwnerCompanyID > 0 and a.Supplier_CompanyID > 0";

    /**
     * 查询线下承运商列表
     *
     * @return
     */
    @Select(QUERY_SQL + " and a.Supplier_Status in (0,1,2)")
    @DataScope(column = "Supplier_OwnerCompanyID", alias = "a")
    List<SupplierDO> list();

    /**
     * 根据id获取信息
     *
     * @param id
     * @return
     */
    @Select(QUERY_SQL + " and a.Supplier_ID = #{id}")
    SupplierDO getById(Long id);

    @Select(QUERY_SQL + " and a.Supplier_OwnerCompanyID = #{tenantId}")
    List<SupplierDO> getByTenantId(Long tenantId);

    /**
     * 获取邀请关系列表
     *
     * @return
     */
    @Select(RELATION_SQL + " order by a.Supplier_ID asc")
    @DataScope(column = "Supplier_OwnerCompanyID", alias = "a")
    List<Relation> getRelationList();

    /**
     * 根据客户id同步邀请关系
     *
     * @param customerId
     * @return
     */
    @Select(RELATION_SQL + " and b.Customer_ID = #{customerId}")
    Relation getRelationByCustomerId(Long customerId);

    /**
     * 根据租户同步邀请关系
     *
     * @param tenantId
     * @return
     */
    @Select(RELATION_SQL + " and b.Customer_OwnerCompany = #{tenantId}")
    List<Relation> getRelationByTenantId(Long tenantId);


    @Select("SELECT top 1 a.Supplier_ID, a.Supplier_GUID, a.Supplier_OwnerCompanyID, a.Supplier_CompanyID, a.Supplier_Status, a.Supplier_StatusTime, a.Supplier_Processor, a.Supplier_Welcome, a.Supplier_Creator, a.Supplier_InsertTime, a.Supplier_Updater, a.Supplier_UpdateTime, a.Supplier_Invalid, a.Supplier_Comments, a.opt_status, a.Supplier_ClientCode, a.Supplier_AutoBalance, a.Supplier_ShortName, a.Supplier_Heat, a.Supplier_Type " +
            ",b.Company_Phone as contactMobile,b.Company_Mail as contactEmail,SUBSTRING(b.Company_Contact,0,50) as contactName,b.Company_Name as name,b.Company_Personal as personal, 1 as type " +
            "FROM TMS_MSupplier a " +
            "INNER JOIN TMS_Company AS b ON a.Supplier_CompanyID = b.Company_ID " +
            "WHERE a.Supplier_OwnerCompanyID > 0 and a.Supplier_CompanyID > 0 " +
            " and a.Supplier_OwnerCompanyID = #{ownerCompanyId} and a.Supplier_CompanyID = #{companyId} and a.Supplier_Status = 1 and a.Supplier_Invalid = 0 ")
    SupplierDO getCompanyId(@Param("ownerCompanyId") Long ownerCompanyId, @Param("companyId") Long companyId);

    @Select("SELECT Supplier_ID from TMS_MSupplier where Supplier_InsertTime >= #{startTime} and Supplier_InsertTime <= #{endTime} ")
    List<Long> getByTime(@Param("startTime") String startTime, @Param("endTime") String endTime);

    @Select("SELECT Supplier_ID from TMS_MSupplier where Supplier_OwnerCompanyID = #{tenantId} and Supplier_InsertTime >= #{startTime} and Supplier_InsertTime <= #{endTime} ")
    List<Long> getByTenantIdTime(@Param("tenantId") Long tenantId, @Param("startTime") String startTime, @Param("endTime") String endTime);
}
